package cn.tedu.jdbc;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.LinkedList;
import java.util.List;

/**
 * 	对JDBC的增删改查进行测试和练习
 * 对一部分代码进行了优化(起码看起不是这样的)
 * */
public class Demo02 {
	public static void main(String[] args) {

		// 声明对象
		Connection connection = null;
		Statement statement = null;

		try {
			// 加载资源
			Class.forName("com.mysql.jdbc.Driver");

			// 获取连接对象
			connection = DriverManager.getConnection(
					"jdbc:mysql://127.0.0.1:3308/db01?characterEncoding=utf-8&serverTimezone=GMT", "root", "lilium");
			// 获取命令对象
			statement = connection.createStatement();

			// 创建一个链表来储存student
			List<Student> students = new LinkedList<Student>();

			/** 增 */
			String insertSQL = "insert student (name,age,major) values ('初音未来',16,1),('Rook',26,3)";

			/** 删 */
			String deleteSQL = "delete from student where name = '初音未来'  ";
			/** 改 */
			String updateSQL = "update student set name = 'DOC' where name = 'rook' ";

			/** 查 */
			String selectSQL = "select s.name '学生姓名',s.age '年龄',m.name '专业' from student s join major m on s.major = m.id ";
			
			/**重新搞一下数据*/
			String  restSQL = "delete from student where name ='DOC' ";
			// 执行SQL命令
			// 增
			updateAndSelect(statement, insertSQL, selectSQL, students);
			// 删
			updateAndSelect(statement, deleteSQL, selectSQL, students);
			// 改
			updateAndSelect(statement, updateSQL, selectSQL, students);
			// 重置列表
			updateAndSelect(statement, restSQL, selectSQL, students);
			
		} catch (Exception e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		} finally {
			// 关闭资源
			close(connection);
			close(statement);

		}

	}

	// 写一个方法来执行update 语句
	public static int updateSQL(Statement statement, String sql) throws SQLException {
		int roll = statement.executeUpdate(sql);
		System.out.println("影响的行数:" + roll);
		return roll;
	}

	// 执行select 语句
	public static List<Student> selectSQL(Statement statement, String sql, List<Student> students) throws SQLException {

		// 执行SQL 语句得到结果集
		ResultSet resultSet = statement.executeQuery(sql);

		// 处理SQL语句
		while (resultSet.next()) {
			students.add(new Student(resultSet.getString("学生姓名"), resultSet.getInt("年龄"), resultSet.getString("专业")));
		}
		// 打印出数据看看
		System.out.println(students);
		return students;
	}

	// 执行方法更新并查看
	public static void updateAndSelect(Statement statement, String updateSQL, String selectSQL, List<Student> students)
			throws SQLException {
		updateSQL(statement, updateSQL);
		selectSQL(statement, selectSQL, students);
		students.clear();
		System.out.println("List 已清空");
	}

	// 执行关闭资源
	public static void close(Object object) {
		// 获取对象名称
		String name = object.getClass().getSimpleName();
		if( object instanceof Statement) {
			try {
				((Statement)object).close();
			} catch (SQLException e) {
				e.printStackTrace();
				object =null;
				System.out.println(name+"已转交CB");
				return;
			}
		}else if (object instanceof Connection) {
			try {
				((Connection)object).close();
			} catch (SQLException e) {
				e.printStackTrace();
				object =null;
				System.out.println(name+"已转交CB");
				return;
			}
		}else {
			 object  = null;
			 System.out.println(name+"已转交CB");
				return;
		}
		System.out.println( name+"已经关闭");
	
	}

}

// 创建Student 类来存放查询到的数据
class Student {
	String name;
	int age;
	String major;

	public Student(String name, int age, String major) {
		super();
		this.name = name;
		this.age = age;
		this.major = major;
	}

	public String getName() {
		return name;
	}

	public void setName(String name) {
		this.name = name;
	}

	public int getAge() {
		return age;
	}

	public void setAge(int age) {
		this.age = age;
	}

	public String getMajor() {
		return major;
	}

	public void setMajor(String major) {
		this.major = major;
	}

	@Override
	public String toString() {
		return "Student [姓名=" + name + ", 年龄=" + age + ", 专业=" + major + "]";
	}
}